const db = require('../config/database');
const { OpenAI } = require('openai');
require('dotenv').config({path: '../.env'});

class SQLGeneratorModel {
  constructor() {
    // 初始化数据库连接池
    this.pool = db.createPool();
    this.initializeTable();


    // 初始化LLM客户端
    this.openai = new OpenAI({
      baseURL: process.env.AI_API_BASE_URL || 'https://api.siliconflow.cn/v1',
      apiKey: process.env.AI_API_KEY
    });
    
    // TODO: 将现有的提示词模板从routes/sql-generator.js移动到这里
    this.promptTemplates = {
      BASIC_PROMPT: `
将以下自然语言查询转换为SQL:

表名: {dbTable}
查询: {description}

仅返回SQL语句，不要包含任何解释或注释。
`,
IMPROVED_PROMPT: `
请将以下自然语言查询转换为SQL查询语句。

数据库表信息:
表名: {dbTable}
字段: review_id(INT), merchant(VARCHAR), rating(TINYINT), score_taste(TINYINT), 
score_environment(TINYINT), score_service(TINYINT), price_per_person(DECIMAL), 
time(VARCHAR), num_thumbs_up(INT), num_response(INT), content_review(TEXT), 
reviewer(VARCHAR), reviewer_value(INT), reviewer_rank(INT), favorite_foods(VARCHAR)

查询需求: {description}

只需返回SQL查询语句，无需任何解释或注释。确保SQL语法正确并适用于MySQL/TiDB。
`,
ADVANCED_PROMPT: `
作为SQL专家，你的任务是将自然语言转换为精确的SQL查询语句。

数据库表详细信息:
表名: {dbTable}
表结构:
- review_id: INT (主键，唯一标识每条评价)
- merchant: VARCHAR(100) (餐厅名称)
- rating: TINYINT (总体评分，1-5分)
- score_taste: TINYINT (口味评分，1-5分)
- score_environment: TINYINT (环境评分，1-5分)
- score_service: TINYINT (服务评分，1-5分)
- price_per_person: DECIMAL(8,2) (人均价格)
- time: VARCHAR(50) (就餐时间，如"2023年1月")
- num_thumbs_up: INT (点赞数量)
- num_response: INT (回复数量)
- content_review: TEXT (评价内容)
- reviewer: VARCHAR(50) (评价人)
- reviewer_value: INT (评价人价值)
- reviewer_rank: INT (评价人等级)
- favorite_foods: VARCHAR(255) (喜爱的食物)

用户查询需求: {description}

示例对应:
1. 自然语言: "找出评分超过4分的餐厅及其评价数量"
   SQL: SELECT merchant, AVG(rating) as avg_rating, COUNT(*) as review_count FROM restaurant_reviews WHERE rating > 4 GROUP BY merchant ORDER BY avg_rating DESC;

2. 自然语言: "计算所有餐厅的平均人均消费"
   SQL: SELECT merchant, AVG(price_per_person) as avg_price FROM restaurant_reviews GROUP BY merchant ORDER BY avg_price;

请生成适用于MySQL/TiDB的SQL查询语句。确保使用正确的聚合函数、条件表达式、排序和分组。只返回SQL查询语句本身，不包含任何解释或注释。
`
    };
  }

  async initializeTable() {
    try {
      const createTableQuery = `
        CREATE TABLE IF NOT EXISTS restaurant_reviews (
          review_id INT PRIMARY KEY,
          merchant VARCHAR(100) NOT NULL,
          rating TINYINT,
          score_taste TINYINT,
          score_environment TINYINT,
          score_service TINYINT,
          price_per_person DECIMAL(8,2),
          time VARCHAR(50),
          num_thumbs_up INT,
          num_response INT,
          content_review TEXT,
          reviewer VARCHAR(50),
          reviewer_value INT,
          reviewer_rank INT,
          favorite_foods VARCHAR(255)
              )
      `;
      
      await this.pool.query(createTableQuery);
      console.log('restaurant_reviews表初始化成功');
    } catch (error) {
      console.error('数据库初始化失败:', error);
      throw error;
    }
  }

  // TODO: 实现selectPromptTemplate方法
  selectPromptTemplate(promptType = 'advanced') {
    switch (promptType) {
      case 'basic': return this.promptTemplates.BASIC_PROMPT;
      case 'improved': return this.promptTemplates.IMPROVED_PROMPT;
      case 'advanced': 
      default: return this.promptTemplates.ADVANCED_PROMPT;
    }
  }

  // TODO: 实现extractSQLFromResponse方法
  extractSQLFromResponse(text) {
    // 从LLM响应中提取SQL代码
    // 尝试移除可能的Markdown代码块标记
    const codeBlockRegex = /```(?:sql)?\n?([\s\S]*?)```/i;
    const match = text.match(codeBlockRegex);
    
    if (match && match[1]) {
        return match[1].trim();
    }
    
    // 如果没有Markdown格式，直接返回文本
    return text.trim();
  }

  // TODO: 实现isSQLSafe方法
  isSQLSafe(sql) {
    // 检查SQL是否安全
  }

  // TODO: 实现generateSQL方法
  async generateSQL(description, dbTable, promptType = 'advanced') {
    // 生成SQL的核心逻辑
    try {
        const startTime = Date.now();
        
        // 选择并填充提示词模板
        let prompt = this.selectPromptTemplate(promptType)
          .replace('{description}', description)
          .replace(/{dbTable}/g, dbTable);
          
        // 调用LLM API
        const completion = await this.openai.chat.completions.create({
          messages: [
            { role: 'system', content: '你是一个能生成精准SQL语句的数据库专家' },
            { role: 'user', content: prompt }
          ],
          model: "Qwen/Qwen2.5-7B-Instruct",
          temperature: 0.1  // 低温度使输出更确定性
        });
        
        const generatedText = completion.choices[0].message.content;
        const processingTime = Date.now() - startTime;
        const tokenUsage = completion.usage ? completion.usage.total_tokens : 'unknown';
        
        // 提取SQL代码（移除可能的Markdown标记等）
        const sql = this.extractSQLFromResponse(generatedText);
        
         
    
        return {
          sql,
          metadata: {
            tokens: tokenUsage,
            time: `${processingTime}ms`,
            prompt_type: promptType
          }
        };
      } catch (error) {
        console.error('SQL生成失败:', error);
        throw new Error(`SQL生成失败: ${error.message}`);
      }
  }

  // TODO: 实现executeSQL方法
  async executeSQL(description, dbTable, promptType = 'advanced') {
    // 生成SQL的核心逻辑
    try {
      const startTime = Date.now();
      
      // 选择并填充提示词模板
      let prompt = this.selectPromptTemplate(promptType)
        .replace('{description}', description)
        .replace(/{dbTable}/g, dbTable);
        
      // 调用LLM API
      const completion = await this.openai.chat.completions.create({
        messages: [
          { role: 'system', content: '你是一个能生成精准SQL语句的数据库专家' },
          { role: 'user', content: prompt }
        ],
        model: "Qwen/Qwen2.5-7B-Instruct",
        temperature: 0.1  // 低温度使输出更确定性
      });
      
      const generatedText = completion.choices[0].message.content;
      const processingTime = Date.now() - startTime;
      const tokenUsage = completion.usage ? completion.usage.total_tokens : 'unknown';
      
      // 提取SQL代码（移除可能的Markdown标记等）
      const sql = this.extractSQLFromResponse(generatedText);
      
      // Execute query against database
      const [queryResults] = await this.pool.query(sql); 
  
      return {
        sql,
        metadata: {
          tokens: tokenUsage,
          time: `${processingTime}ms`,
          prompt_type: promptType
        },
        queryResults: queryResults
      };
    } catch (error) {
      console.error('SQL执行失败:', error);
      throw new Error(`SQL执行失败: ${error.message}`);
    }
  }
}

module.exports = new SQLGeneratorModel();